package database;
import java.sql.*;

import model.Suspend;
import oracle.jdbc.pool.OracleDataSource;

public class SuspendOperations 
{
	private Connection conn;
	private Statement stm;
	private ResultSet rst;
	private PreparedStatement prst;
	public SuspendOperations()
	{
		conn=openDB();
	}
	// open database connection
	public Connection openDB()
	{
		try 
		{
			OracleDataSource ods = new OracleDataSource();

			if ( test.Main.dataConn == "GaryTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106491");
				ods.setPassword("db13May85");
			}
			else if ( test.Main.dataConn == "KeythTallaght"){
				ods.setURL("jdbc:oracle:thin:@//10.10.2.7:1521/global1");
				ods.setUser("X00106271");
				ods.setPassword("db19Jan82");
			}
			else if ( test.Main.dataConn == "KeythHome"){
				ods.setURL("jdbc:oracle:thin:HR/localhost:1521:XE");
				ods.setUser("System");
				ods.setPassword("7481600kW");
			}
			else if ( test.Main.dataConn == "GaryHome"){
				ods.setURL("jdbc:oracle:thin:HR/SYSTEM@localhost:1521:XE");
				ods.setUser("SYSTEM");
				ods.setPassword("1nf1n1ty");
			}
			
			conn = ods.getConnection();
			System.out.println("connected.");
		} 
		catch (Exception e) 
		{
			System.out.print("Unable to load driver " + e);
			System.exit(1);
		}
		return conn;
	}
	// drop suspend table from database
	public void dropSuspendTable() 
	{
		System.out.println("Checking for existing tables.");
		try
		{
			stm = conn.createStatement();

			try 
			{
				// Dropping a sequence
				String dropseq = "drop sequence id_seq";
				stm = conn.prepareStatement(dropseq);
				stm.executeUpdate(dropseq);

				stm.execute("DROP TABLE Suspend");
				System.out.println("Suspend table dropped.");
			} 
			catch (SQLException ex) 
			{
			
			}
		} 
		catch (SQLException ex) 
		{
			System.out.println("ERROR: " + ex.getMessage());
			ex.printStackTrace();
		}
	}
	// create an suspend table in database
	public void CreateSuspendTable() 
	{
		try 
		{
			stm = conn.createStatement();
			String create = "CREATE TABLE Suspend "
					+ "(sus_num NUMBER PRIMARY KEY, ord_num Number, start VARCHAR(20), end VARCHAR(20))";

			stm.executeUpdate(create);

			// Creating a sequence
			String createseq = "create sequence sus_num_seq increment by 1 start with 1 nocache";
			stm = conn.createStatement();
			stm.executeUpdate(createseq);
		} 
		catch (SQLException e) 
		{
			System.out.print("SQL Exception " + e);
			System.exit(1);
		}
	}
	// print all suspensions in table
	public void querySuspend() 
	{
		try 
		{
			String queryString = "SELECT * FROM Suspend";
			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
			System.out.println("Suspend Table");
			while (rst.next()) 
			{
				System.out.println(rst.getInt(1) + " " + rst.getInt(2)
						+ " " + rst.getString(3) + " " + rst.getString(4));
			}
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// delete an suspension from table
	public void deleteSuspend(int i) 
	{
		try 
		{
			String cmd = "DELETE FROM Suspend WHERE sus_num =" + i;
			stm = conn.createStatement();
			stm.executeUpdate(cmd);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
	}
	// add an suspension to the table
	public void addSuspend(Suspend e) 
	{
		try 
		{
			String sql = "INSERT INTO Suspend(sus_num, ord_num, start, end) VALUES (sus_num_seq.nextVal,?,?,?)";

			prst = conn.prepareStatement(sql);
			prst.setInt(1, e.getOrdNum());
			prst.setString(2, e.getStart());
			prst.setString(3, e.getEnd());

			prst.executeUpdate();
		} 
		catch (Exception se) 
		{
			System.out.println(se);
		}
	}
	// return a result set of all suspensions in table
	public ResultSet getSuspend() 
	{
		try 
		{
			String queryString = "SELECT * FROM Suspend ORDER BY sus_num";

			stm = conn.createStatement();
			rst = stm.executeQuery(queryString);
		} 
		catch (Exception e) 
		{
			System.out.println(e);
		}
		return rst;
	}
	// update the start of a suspension
	public void updateSuspendStart(int i,String s) 
	{
		try 
		{
			String sql = "UPDATE Suspend SET start=" + s + "where ord_num="
					+ i;

			stm = conn.createStatement();
			stm.executeUpdate(sql);
		} 
		catch (Exception e) 
		{
			System.out.println("Problem" + e);
		}
	}
	// update the end of a suspension
		public void updateSuspendEnd(int i,String s) 
		{
			try 
			{
				String sql = "UPDATE Suspend SET end=" + s + "where ord_num="
						+ i;

				stm = conn.createStatement();
				stm.executeUpdate(sql);
			} 
			catch (Exception e) 
			{
				System.out.println("Problem" + e);
			}
		}
	// close the connection to database
	public void closeDB() 
	{
		try 
		{
			stm.close();
			rst.close();
			conn.close();
			System.out.print("Connection closed");
		} 
		catch (SQLException e) 
		{
			System.out.print("Could not close connection ");
			e.printStackTrace();
		}
	}
}
